Outbound_Process

 
Documentation generated by Matillion ETL

Job: Organisation ORC

Variables:

Name
Visibility
Behaviour
Type
Default Value
Description
currentDT
Public
Shared
Text
 
 

get current date time

Python Script
Parameter
Value
Script
from datetime import datetime

currentDT = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print (currentDT)
context.updateVariable("currentDT",currentDT)
Interpreter
Python 3
Timeout
360

Organization

Run Transformation
Parameter
Value
Transformation Job
Organization
Set Scalar Variables
Set Grid Variables

Organisation Unload

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_organisation
S3 URL Location
${ONEMD_S3_Target_Folder}
S3 Object Prefix
ETH MDM21 Organization.txt
IAM Role Arn
arn:aws:iam::<aws-account-id>:role/<role-name>
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
No
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

Set Current Table File For Archive

Python Script
Parameter
Value
Script
context.updateVariable('Archive_temp_current_table', 'outbound_dest_organisation')
context.updateVariable('Archive_temp_current_file', 'ETH MDM21 Organization.txt')
context.updateVariable('Archive_type','ONEMD')
Interpreter
Python 3
Timeout
360

Archive Outbound Global Function 0

Run Orchestration
Parameter
Value
Orchestration Job
Archive Outbound Global Function
Set Scalar Variables
Set Grid Variables

Organisation Transfer

Data Transfer Object
Parameter
Value
Source Type
S3
Source URL
s3://ethicon/outbound/One MD Testing /Output files/ETH MDM21 Organization.txt000
Unpack ZIP file
No
Target Type
SFTP
Gzip data
No
Target Object Name
ETH MDM21 Organization.txt
Set Home Directory as Root
No
Target URL
https://smft.axtria.com/OneMD
Target Username
JnJ_Ethicon
Target Password
********
Target SFTP Key

Job: Organization

Variables:

Name
Visibility
Behaviour
Type
Default Value
Description
v_CURRENT_DATE
Public
Copied
DateTime
2019-01-01
 

outbound_dest_fiscal_calendar

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_dest_fiscal_calendar
Column Names
efftv_start_dt, efftv_end_dt, datamonth, fiscal_year_month, fiscal_year_wk_start_dt, fiscal_year_wk_end_dt
Trim Columns
No

Get End Date

Join
Parameter
Value
Main Table
Get Start Date
Main Table Alias
start
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal
Output Columns
start.algn_struc_cd, algn_struc_cd, start.geo_id, geo_id, start.geo_nm, geo_nm, start.level_cd, level_cd, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Geo convert to Fiscal

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE
WHEN "fiscal_year_wk_start_dt" is NULL
THEN '1900-01-01'
ELSE "fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE
WHEN "fiscal_year_wk_end_dt" is NULL
THEN '9999-12-31'
ELSE "fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("geo_id"), geo_id_length

Geo Id =2 digit

Filter
Parameter
Value
Filter Conditions
geo_id_length, Is, Equal to, 2
Combine Conditions
AND

Expand by Upper Div

Join
Parameter
Value
Main Table
Geo Id =2 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Expand by Upper Div (included recs)

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Not, Null or blank
Combine Conditions
AND

Merge Terr and Div

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case
when "efftv_start_dt">"new_efftv_start_dt"
then "efftv_start_dt"
else "new_efftv_start_dt"
end, EFFTV_START_DT_2, case
when "efftv_end_dt">"new_efftv_end_dt"
then "new_efftv_end_dt"
else "efftv_end_dt"
end, EFFTV_END_DT_2, case
when "level_cd"='Nation'
then "lower_algn_struc_cd"
else "geo_id"
end, GEO_ID_2

Refined output 2

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, algn_struc_cd, geo_id_2, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt_2, efftv_start_dt, efftv_end_dt_2, efftv_end_dt

Combine Terr and Mgr

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Filter on dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag

flag(included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Get Upper Territory Level

Join
Parameter
Value
Main Table
Update Upper Terr & Constants
Main Table Alias
c1
Joins
flag(included recs), c2, Inner
Join Expressions
"c1"."algn_struc_cd" = "c2"."algn_struc_cd"
and "c1"."upper_geo_id" = "c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, upper_geo_id, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c2.level_cd, upper_level_cd

Update Final Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"team_id"
, geo_id_new, left("upper_level_cd",1), PARENT ORGANIZATION ID, trim("geo_nm")
, geo_nm_new, case
when left("level_cd",1) = 'S'
then 'T'
else left("level_cd",1)
end
, ORGANIZATION ID, case
when DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) ='9999'
then NULL
else "efftv_end_dt"
end, end_date_new

Update Final Constants(output)

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, team_id, team_id, level_cd, level_cd, org id prefix, org id prefix, franchise code, franchise code, sales org division code, sales org division code, sales org name, sales org name, upper_geo_id, upper_geo_id, provider id, provider id, last_updated_dt, last_updated_dt, efftv_start_dt, efftv_start_dt, end_date_new, efftv_end_dt, upper_level_cd, upper_level_cd, geo_id_new, geo_id, parent organization id, parent organization id, geo_nm_new, geo_nm, organization id, organization id

Aggregate Before Export

Aggregate
Parameter
Value
Groupings
provider id, geo_id, organization id, parent organization id, upper_geo_id, efftv_start_dt, efftv_end_dt, last_updated_dt, algn_struc_cd
Aggregations
geo_nm, Max, sales org division code, Max, sales org name, Max, franchise code, Max

Get Alignment Str Name

Join
Parameter
Value
Main Table
Aggregate Before Export
Main Table Alias
c1
Joins
Param: Alignment Structure, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
, c1_Left_c2
Output Columns
c1.provider id, provider id, c1.geo_id, geo_id, c1.organization id, organization id, c1.parent organization id, parent organization id, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt, c1.algn_struc_cd, algn_struc_cd, c1.max_geo_nm, geo_nm, c1.max_sales org division code, sales org division code, c1.max_sales org name, sales org name, c1.max_franchise code, franchise code, c2.algn_struc_nm, algn_struc_nm

Get Terr Category and Terr Type

Join
Parameter
Value
Main Table
Get Alignment Str Name
Main Table Alias
c1
Joins
outbound_src_geo_attributes, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
and
"c1"."geo_id"="c2"."geo_id"
and
("c1"."efftv_start_dt" between "c2"."efftv_start_dt" and "c2"."efftv_end_dt"), c1_Left_c2
Output Columns
c1.provider id, provider id, c1.geo_id, geo_id, c1.organization id, organization id, c1.parent organization id, parent organization id, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt, c1.algn_struc_cd, algn_struc_cd, c1.geo_nm, geo_nm, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.franchise code, franchise code, c1.algn_struc_nm, algn_struc_nm, c2.attribute_cat, attribute_cat, c2.attribute_type, attribute_type

Final Calculation for dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case when "efftv_start_dt" is NULL
then ''
else
DATE_PART_YEAR(to_date("efftv_start_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("efftv_start_dt",'YYYY-mm-dd'))),2,'00')
end
, start date new, case when "efftv_end_dt" is NULL
then ''
else
DATE_PART_YEAR(to_date("efftv_end_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("efftv_end_dt",'YYYY-mm-dd'))),2,'00')
end
, end date new, case when "last_updated_dt" is NULL
then ''
else
DATE_PART_YEAR(to_date("last_updated_dt",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated_dt",'YYYY-mm-dd'))),2,'00')
end
, last updated new, Left("sales org name",30), sales org name

outbound_dest_organisation

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_organisation
Fix Data Type Mismatches
Yes
Column Mapping
provider id, provider id, geo_id, organization id, organization id, organization type code, geo_nm, organization name, sales org division code, sales org division code, sales org name, sales org team code, franchise code, franchise code, upper_geo_id, parent organization id, parent organization id, parent organization type code, start date new, organization effective date, end date new, organization end date, last updated new, last update date, algn_struc_cd, account alignment structure code, algn_struc_nm, account alignment structure name, attribute_cat, territory position category, attribute_type, territory position type
Truncate
Truncate
Automatic Compression
No

Get Oldest

Join
Parameter
Value
Main Table
Update Final Constants(output)
Main Table Alias
c1
Joins
Agg for Oldest, c2, Inner
Join Expressions
"c1"."geo_id"="c2"."geo_id"
and
"c1"."parent organization id"="c2"."parent organization id"
and
"c1"."upper_geo_id"="c2"."upper_geo_id"
and
"c1"."organization id"="c2"."organization id"
and
"c1"."efftv_start_dt"="c2"."min_efftv_start_dt", c1_Inner_c2
Output Columns
c1.algn_struc_cd, c1_algn_struc_cd, c1.geo_id, c1_geo_id, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c1.upper_geo_id, c1_upper_geo_id, c1.provider id, provider id, c1.last_updated_dt, last_updated_dt, c1.efftv_start_dt, efftv_start_dt, c1.upper_level_cd, upper_level_cd, c1.parent organization id, c1_parent organization id, c1.geo_nm, geo_nm, c1.organization id, c1_organization id, c1.efftv_end_dt, efftv_end_dt

Agg for Oldest

Aggregate
Parameter
Value
Groupings
geo_id, organization id, parent organization id, upper_geo_id, algn_struc_cd
Aggregations
efftv_start_dt, Min

Get Original Align Struct

Join
Parameter
Value
Main Table
Get Upper Territory Level
Main Table Alias
gutl
Joins
Filter Active Geographies, fag, Left
Join Expressions
"gutl"."geo_id" = "fag"."geo_id", gutl_Left_fag
Output Columns
gutl.algn_struc_cd, algn_struc_cd, gutl.geo_id, geo_id, gutl.team_id, team_id, gutl.level_cd, level_cd, gutl.geo_nm, geo_nm, gutl.efftv_start_dt, efftv_start_dt, gutl.efftv_end_dt, efftv_end_dt, gutl.org id prefix, org id prefix, gutl.franchise code, franchise code, gutl.sales org division code, sales org division code, gutl.sales org name, sales org name, gutl.upper_geo_id, upper_geo_id, gutl.provider id, provider id, gutl.last_updated_dt, last_updated_dt, gutl.upper_level_cd, upper_level_cd, fag.algn_struc_cd, fag_algn_struc_cd

Update Sales Org Name

Join
Parameter
Value
Main Table
Get Original Align Struct
Main Table Alias
goas
Joins
MDM LOV Parameter, lovp, Left
Join Expressions
"goas"."fag_algn_struc_cd" = "lovp"."alignment structure code", goas_Left_lovp
Output Columns
goas.algn_struc_cd, algn_struc_cd, goas.geo_id, geo_id, goas.team_id, team_id, goas.level_cd, level_cd, goas.geo_nm, geo_nm, goas.efftv_start_dt, efftv_start_dt, goas.efftv_end_dt, efftv_end_dt, goas.org id prefix, org id prefix, goas.franchise code, franchise code, goas.sales org division code, sales org division code, goas.upper_geo_id, upper_geo_id, goas.provider id, provider id, goas.last_updated_dt, last_updated_dt, goas.upper_level_cd, upper_level_cd, lovp.sales org name, sales org name, goas.fag_algn_struc_cd, fag_algn_struc_cd

Exclude Rolling to 80

Join
Parameter
Value
Main Table
flag(included recs)
Main Table Alias
c1
Joins
Combine Rolling to 80, c2, Left
Join Expressions
"c1"."algn_struc_cd" = "c2"."lower_algn_struc_cd"
and "c1"."geo_id" = "c2"."lower_geo_id", c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.geo_id, team_id, c2.lower_algn_struc_cd, lower_algn_struc_cd

Exclude Rolling to 80 (included recs)

Filter
Parameter
Value
Filter Conditions
lower_algn_struc_cd, Is, Null or blank
Combine Conditions
AND

Get Valid Teams

Join
Parameter
Value
Main Table
Filter CG Team Geo Assoc
Main Table Alias
c1
Joins
Exclude Rolling to 80 (included recs), c2, Inner
Join Expressions
"c1"."algn_struc_cd" = "c2"."algn_struc_cd"
and "c1"."geo_id" = "c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt

Get Max Team ID

Join
Parameter
Value
Main Table
Get Valid Teams
Main Table Alias
c1
Joins
Agg for Largest Team Record, c2, Inner
Join Expressions
"c1"."algn_struc_cd" = "c2"."algn_struc_cd"
and "c1"."split_pct" = "c2"."max_split_pct"
and "c1"."team_id" = "c2"."team_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.split_pct, split_pct, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.last_updated_dt, last_updated_dt

One Record Per Team

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
geo_id, Max

Get Team Info

Join
Parameter
Value
Main Table
One Record Per Team
Main Table Alias
c1
Joins
Combine Terr and Mgr, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."algn_struc_cd"
and
"c1"."max_geo_id"="c2"."geo_id", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.max_geo_id, geo_id, c2.level_cd, level_cd, c2.geo_nm, geo_nm, c2.efftv_start_dt, efftv_start_dt, c2.efftv_end_dt, efftv_end_dt, c1.team_id, team_id

Combine Geos and Teams

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Get Organization ID

Join
Parameter
Value
Main Table
Combine Geos and Teams
Main Table Alias
c1
Joins
outbound_src_mdm_lov_param, c2, Inner
Join Expressions
"c1"."algn_struc_cd"="c2"."alignment structure code", c1_Inner_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.team_id, team_id, c2.org id prefix, org id prefix, c2.franchise code, franchise code, c2.sales org division code, sales org division code, c2.sales org name, sales org name

Filter_dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag

Get Organization ID (included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Get Upper Terr & Dates

Join
Parameter
Value
Main Table
Get Organization ID (included recs)
Main Table Alias
c1
Joins
included recs, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."lower_algn_struc_cd"
and
"c1"."geo_id"="c2"."lower_geo_id"
and
"c1"."efftv_start_dt"<="c2"."efftv_end_dt"
and
"c1"."efftv_end_dt">="c2"."efftv_start_dt"
, c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.team_id, team_id, c1.level_cd, level_cd, c1.geo_nm, geo_nm, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt, c1.org id prefix, org id prefix, c1.franchise code, franchise code, c1.sales org division code, sales org division code, c1.sales org name, sales org name, c2.upper_geo_id, upper_geo_id, c2.efftv_start_dt, new_efftv_start_dt, c2.efftv_end_dt, new_efftv_end_dt

Update Upper Terr & Constants

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case
when "upper_geo_id"="algn_struc_cd"
then "geo_id"
else "upper_geo_id"
end
, upper_geo_id, 'OPCO_ETH', Provider ID, CURRENT_DATE, LAST_UPDATED_DT, case
when "efftv_start_dt">"new_efftv_start_dt"
then "efftv_start_dt"
else "new_efftv_start_dt"
end
, efftv_start_dt, case
when "efftv_end_dt">"new_efftv_end_dt"
then "new_efftv_end_dt"
else "efftv_end_dt"
end
, efftv_end_dt

Aggregate for Prior Org

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
algn_struc_cd, Max

outbound_dest_dl_prior_org

Table Output
Parameter
Value
Schema
${Schema_Default}
Target Table Name
outbound_dest_dl_prior_org
Fix Data Type Mismatches
No
Column Mapping
team_id, geo_id, max_algn_struc_cd, algn_struc_cd
Truncate
Truncate
Automatic Compression
No

Refined_output

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, geo_id, team_id

Geo Id <4 digit

Filter
Parameter
Value
Filter Conditions
geo_id_length, Not, Equal to, 4
Combine Conditions
AND

Expand by Upper

Join
Parameter
Value
Main Table
Geo Id <4 digit
Main Table Alias
c1
Joins
level CD is territory, c2, Left
Join Expressions
"c1"."algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new"
, c1_Left_c2
Output Columns
c1.algn_struc_cd, algn_struc_cd, c1.geo_id, geo_id, c1.geo_nm, geo_nm, c1.level_cd, level_cd, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c1.last_updated_dt_new, last_updated_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo Id =4 digit

Filter
Parameter
Value
Filter Conditions
geo_id_length, Is, Equal to, 4
Combine Conditions
AND

Refined output

Rename
Parameter
Value
Column Mapping
algn_struc_cd, algn_struc_cd, geo_id, geo_id, level_cd, level_cd, geo_nm, geo_nm, efftv_start_dt_new, efftv_start_dt, efftv_end_dt_new, efftv_end_dt

Param Geo Hier End

Join
Parameter
Value
Main Table
Param Geo Hier Start
Main Table Alias
geo_hier
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"geo_hier"."geo_hier_efftv_end_dt"="fiscal"."efftv_end_dt", geo_hier_Left_fiscal
Output Columns
geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.geo_hier_efftv_start_dt, efftv_start_dt, geo_hier.geo_hier_efftv_end_dt, efftv_end_dt, geo_hier.level_cd, level_cd, geo_hier.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Param Geo Hier Calc Dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE
WHEN "fiscal_year_wk_start_dt" is NULL
THEN '1900-01-01'
ELSE "fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE
WHEN "fiscal_year_wk_end_dt" is NULL
THEN '9999-12-31'
ELSE "fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new

level CD is territory

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Equal to, Territory
Combine Conditions
AND

Geo Hier Expand by Upper Geo

Join
Parameter
Value
Main Table
Geo Hier: Filter Exclude Upper Struc Not Equal Lower
Main Table Alias
c1
Joins
level CD is territory, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new"
, c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo Hier Merge Terr and Div

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Geo Hier Get Dates for Upper

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
case
when "efftv_start_dt">"new_efftv_start_dt"
then "efftv_start_dt"
else "new_efftv_start_dt"
end, EFFTV_START_DT_2, case
when "efftv_end_dt">"new_efftv_end_dt"
then "new_efftv_end_dt"
else "efftv_end_dt"
end, EFFTV_END_DT_2, case
when len("lower_geo_id")=2
then "lower_algn_struc_cd"
else "upper_geo_id"
end, UPPER_GEO_ID_2

Geo Hier Get Dates for Upper(included recs)

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, lower_algn_struc_cd, upper_algn_struc_cd, upper_geo_id_2, upper_geo_id, efftv_start_dt_2, efftv_start_dt, efftv_end_dt_2, efftv_end_dt

Geo Hier Merge Terr and Upper

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Filter dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt"<=CURRENT_DATE
and
"efftv_end_dt">=CURRENT_DATE, flag

Geo Hier Merge Terr and Upper (included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Get Divisions Rolling to 80

Filter
Parameter
Value
Filter Conditions
upper_geo_id, Is, Equal to, 80
Combine Conditions
AND

Rename output

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt

Combine Rolling to 80

Unite
Parameter
Value
Method
All Columns
Cast Types
Yes
Add Source Component Column
No
Remove duplicates
No

Get Territories Rolling to 80

Join
Parameter
Value
Main Table
Geo Hier Merge Terr and Upper (included recs)
Main Table Alias
c1
Joins
Get Divisions Rolling to 80, c2, Inner
Join Expressions
"c1"."upper_algn_struc_cd"="c2"."lower_algn_struc_cd"
and
"c1"."upper_geo_id"="c2"."lower_geo_id", c1_Inner_c2
Output Columns
c1.lower_algn_struc_cd, lower_algn_struc_cd, c1.lower_geo_id, lower_geo_id, c1.upper_algn_struc_cd, upper_algn_struc_cd, c1.upper_geo_id, upper_geo_id, c1.efftv_start_dt, efftv_start_dt, c1.efftv_end_dt, efftv_end_dt

included recs

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true
Combine Conditions
AND

Geo Hier Merge Terr and Upper (geo id =80)

Filter
Parameter
Value
Filter Conditions
lower_geo_id, Is, Equal to, 80
Combine Conditions
AND

level CD is division

Filter
Parameter
Value
Filter Conditions
level_cd, Is, Equal to, Division
Combine Conditions
AND

Geo Hier Expand by Upper Reg

Join
Parameter
Value
Main Table
Geo_Id =2 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Geo Hier Expand by Upper Div

Join
Parameter
Value
Main Table
Geo_Id =3 digit
Main Table Alias
c1
Joins
level CD is division, c2, Left
Join Expressions
"c1"."upper_algn_struc_cd"="c2"."upper_algn_struc_cd"
and
"c1"."lower_algn_struc_cd"="c2"."lower_algn_struc_cd"
and
"c1"."efftv_start_dt_new"<="c2"."efftv_end_dt_new"
and
"c1"."efftv_end_dt_new">="c2"."efftv_start_dt_new", c1_Left_c2
Output Columns
c1.upper_geo_id, upper_geo_id, c1.lower_geo_id, lower_geo_id, c1.efftv_start_dt_new, efftv_start_dt, c1.efftv_end_dt_new, efftv_end_dt, c2.lower_algn_struc_cd, lower_algn_struc_cd, c2.efftv_start_dt_new, new_efftv_start_dt, c2.efftv_end_dt_new, new_efftv_end_dt

Get Start Date

Join
Parameter
Value
Main Table
outbound_src_geo_management
Main Table Alias
filter
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal
Output Columns
filter.algn_struc_cd, algn_struc_cd, filter.geo_id, geo_id, filter.geo_nm, geo_nm, filter.level_cd, level_cd, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated_dt, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Param Geo Hier Start

Join
Parameter
Value
Main Table
outbound_src_param_geo_hierarchy
Main Table Alias
geo_hier
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"geo_hier"."efftv_start_dt"="fiscal"."efftv_start_dt", geo_hier_Left_fiscal
Output Columns
geo_hier.upper_algn_struc_cd, upper_algn_struc_cd, geo_hier.lower_algn_struc_cd, lower_algn_struc_cd, geo_hier.efftv_start_dt, geo_hier_efftv_start_dt, geo_hier.efftv_end_dt, geo_hier_efftv_end_dt, geo_hier.level_cd, level_cd, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Geo Hier Start Date

Join
Parameter
Value
Main Table
Geo Hier: Rename
Main Table Alias
filter
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"filter"."efftv_start_dt"="fiscal"."efftv_start_dt", filter_Left_fiscal
Output Columns
filter.lower_algn_struc_cd, lower_algn_struc_cd, filter.lower_geo_id, lower_geo_id, filter.upper_algn_struc_cd, upper_algn_struc_cd, filter.upper_geo_id, upper_geo_id, filter.efftv_start_dt, filter_efftv_start_dt, filter.efftv_end_dt, filter_efftv_end_dt, filter.last_updated, last_updated, fiscal.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt

Geo Hier End Date

Join
Parameter
Value
Main Table
Geo Hier Start Date
Main Table Alias
start
Joins
outbound_dest_fiscal_calendar, fiscal, Left
Join Expressions
"start"."filter_efftv_end_dt"="fiscal"."efftv_end_dt", start_Left_fiscal
Output Columns
start.lower_algn_struc_cd, lower_algn_struc_cd, start.lower_geo_id, lower_geo_id, start.upper_algn_struc_cd, upper_algn_struc_cd, start.upper_geo_id, upper_geo_id, start.filter_efftv_start_dt, filter_efftv_start_dt, start.filter_efftv_end_dt, filter_efftv_end_dt, start.last_updated, last_updated, start.fiscal_year_wk_start_dt, fiscal_year_wk_start_dt, fiscal.fiscal_year_wk_end_dt, fiscal_year_wk_end_dt

Geo Hier Convert to Fiscal

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
CASE
WHEN "fiscal_year_wk_start_dt" is NULL
THEN '1900-01-01'
ELSE "fiscal_year_wk_start_dt"
END, EFFTV_START_DT_new, CASE
WHEN "fiscal_year_wk_end_dt" is NULL
THEN '9999-12-31'
ELSE "fiscal_year_wk_end_dt"
END, EFFTV_END_DT_new, DATE_PART_YEAR(to_date("last_updated",'YYYY-mm-dd')) || lpad(extract(month from(to_date("last_updated",'YYYY-mm-dd'))),2,'00') ||
lpad(extract(day from(to_date("last_updated",'YYYY-mm-dd'))),2,'00'), LAST_UPDATED_DT_new, len("lower_geo_id"), len_geo_id, "upper_algn_struc_cd"!="lower_algn_struc_cd", compare struc code

Geo_Id =3 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 3
Combine Conditions
AND

Filter Geo Hier

Filter
Parameter
Value
Filter Conditions
len_geo_id, Not, Equal to, 4
Combine Conditions
AND

Geo Hier: Filter Exclude Upper Struc Not Equal Lower

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{Filter Geo Hier})
WHERE (NOT(("upper_geo_id" = '80')
AND UPPER_ALGN_STRUC_CD <> LOWER_ALGN_STRUC_CD))

Geo_Id =4 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 4
Combine Conditions
AND

Geo_Id =4 digit (included recs)

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt_new, efftv_start_dt, efftv_end_dt_new, efftv_end_dt

Geo_Id =2 digit

Filter
Parameter
Value
Filter Conditions
len_geo_id, Is, Equal to, 2
Combine Conditions
AND

outbound_src_mdm_lov_param

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

Param: Alignment Structure

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_alignment_structures
Column Names
algn_struc_cd, algn_struc_nm, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

outbound_src_geo_attributes

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_attributes
Column Names
algn_struc_cd, geo_id, attribute_cat, attribute_type, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

outbound_src_param_geo_hierarchy

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_geo_hierarchy_structure
Column Names
upper_algn_struc_cd, lower_algn_struc_cd, efftv_start_dt, efftv_end_dt, level_cd
Trim Columns
No

outbound_src_geo_hierarchy

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_param_geo_hierarchy
Column Names
lower_algn_struc_cd, lower_geo_id, upper_algn_struc_cd, upper_geo_id, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Geo Hier: Rename

Rename
Parameter
Value
Column Mapping
lower_algn_struc_cd, lower_algn_struc_cd, lower_geo_id, lower_geo_id, upper_algn_struc_cd, upper_algn_struc_cd, upper_geo_id, upper_geo_id, efftv_start_dt, efftv_start_dt, efftv_end_dt, efftv_end_dt, last_updated_dt, last_updated

outbound_src_geo_management

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, geo_nm, level_cd, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

outbound_src_cust_team_geo_association

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_customerteam_geo_assoc
Column Names
algn_struc_cd, team_id, geo_id, split_pct, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

calculate dates

Calculator
Parameter
Value
Include Input Columns
Yes
Calculations
"efftv_start_dt" <= CURRENT_DATE
and "efftv_end_dt" >= CURRENT_DATE, flag, "team_id" like 'T%', team_id-refined

Cust Team Geo (Included recs)

Filter
Parameter
Value
Filter Conditions
flag, Is, Equal to, true, team_id-refined, Is, Equal to, true
Combine Conditions
AND

Filter CG Team Geo Assoc

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{outbound_src_cust_team_geo_association})
WHERE ("efftv_start_dt" <= CAST(getdate() as date)
AND "efftv_end_dt" >= CAST(getdate() as date)
AND "team_id" ILIKE 't%')

Agg for Largest Team Record

Aggregate
Parameter
Value
Groupings
algn_struc_cd, team_id
Aggregations
split_pct, Max

Filter CG Team Geo Assoc1

Filter
Parameter
Value
Filter Conditions
efftv_start_dt, Is, Less than or equal to, CAST('${v_CURRENT_DATE.now().format("yyyy/MM/dd")}' as DATE), efftv_end_dt, Is, Greater than or equal to, CAST('${v_CURRENT_DATE.now().format("yyyy/MM/dd")}' as DATE), team_id, Is, Ilike, t%
Combine Conditions
AND

CG Geographies Mgmt

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_synygy_geo_master_outbound
Column Names
algn_struc_cd, geo_id, geo_nm, level_cd, efftv_start_dt, efftv_end_dt, last_updated_dt
Trim Columns
No

Filter Active Geographies

SQL
Parameter
Value
SQL Query
SELECT
*
FROM ($T{CG Geographies Mgmt})
WHERE ("efftv_start_dt" <= CAST(getdate() as DATE)
AND "efftv_end_dt" >= CAST(getdate() as DATE))

MDM LOV Parameter

Table Input
Parameter
Value
Schema
${Schema_Default}
Table Name
outbound_src_mdm_lov_param
Column Names
alignment structure code, franchise code, org id prefix, sales org division code, sales org name, default bill-to, default pay-from, updated by, updated date
Trim Columns
No

Job: Archive Outbound Global Function

Add TimeStamp & Get File,Table Information from context of the job run

Python Script
Parameter
Value
Script
import datetime

x = datetime.datetime.now()
x=str(x).split('.')

x=x[0]
print('time_stamp :'+str(x))
print('Archive_temp_current_table :'+str(Archive_temp_current_table))
print('Archive_temp_current_file :'+str(Archive_temp_current_file))

Archive_temp_current_file=Archive_temp_current_file+'_'+x+'__'
print('Archive_temp_current_file upt :'+str(Archive_temp_current_file))
context.updateVariable('Archive_temp_current_file', str(Archive_temp_current_file))
print('Archive_type :'+Archive_type)


#dynamically change path for Archive
if Archive_type=='ONEMD':
S3_temp_Archive_Location=S3_ONEMD_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
elif Archive_type=='MDM21':
S3_temp_Archive_Location=S3_MDM21_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))
else:
S3_temp_Archive_Location=S3_EUSS_Archive_Location
print('Archive Locations :'+str(S3_temp_Archive_Location))




Interpreter
Jython

If ONEMD

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, ONEMD
Combine Conditions
And

S3 Archive(ONEMD)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_ONEMD_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If MDM21

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, MDM21
Combine Conditions
And

S3 Archive(MDM21)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_MDM21_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None

If EUSS

If
Parameter
Value
Mode
Simple
Condition
Archive_type, Is, Equal to, EUSS
Combine Conditions
And

S3 Archive(EUSS)

S3 Unload
Parameter
Value
Schema
${Schema_Default}
Table Name
${Archive_temp_current_table}
S3 URL Location
${S3_EUSS_Archive_Location}
S3 Object Prefix
${Archive_temp_current_file}
IAM Role Arn
arn:aws:iam::775229046089:role/RedshiftS3Athna
Generate Manifest
No
Data File Type
Delimited
Delimiter
|
Compress Data
Yes
Compression Type
GZIP
Null As
Escape
No
Allow Overwrites
Yes
Parallel
No
Add Quotes
No
Max File Size (MB)
Include Header
No
Encryption
None